<?php
namespace Console\Database\Migrations;

use Console\System\Migration;

class rent_car extends Migration
{
    /**
     * 创建数据表
     */
    public function up()
    {
        $sql = <<<sql
        DROP TABLE IF EXISTS `rent_car`;
        CREATE TABLE `rent_car` (
          `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
          `site` varchar(255) DEFAULT NULL,
          `city` varchar(255) DEFAULT NULL,
          `brand` varchar(255) DEFAULT NULL,
          `price` int(11) DEFAULT NULL,
          `pic` varchar(255) DEFAULT NULL,
          `desc` varchar(255) DEFAULT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8 COMMENT='新增数据表';

 
        INSERT INTO `rent_car` VALUES ('1', '1', 'SH', '大众朗逸', '100', '/static/images/ly.jpg', '三厢 | 1.6自动 | 乘坐5人');
        INSERT INTO `rent_car` VALUES ('2', '1', 'SH', '别克英朗', '500', '/static/images/excelle.jpg', '\r\n三厢 | 1.5自动 | 乘坐5人');
        INSERT INTO `rent_car` VALUES ('3', '1', 'SH', '比亚迪秦', '200', '/static/images/byd_qin.jpg', '新能源 | 1.5T自动沪牌 | 乘坐5人 | 沪牌');
        INSERT INTO `rent_car` VALUES ('4', '1', 'SH', '雪佛兰', '235', '/static/images/klz.jpg', '三厢 | 1.6自动 | 乘坐5人');
        INSERT INTO `rent_car` VALUES ('5', '1', 'SH', '别克威朗', '200', '/static/images/2017WL.jpg', '三厢 | 1.5自动 | 乘坐5人');
        INSERT INTO `rent_car` VALUES ('6', '2', 'SH', '大众朗逸', '100', '/static/images/ly.jpg', '三厢 | 1.6自动 | 乘坐5人');
        INSERT INTO `rent_car` VALUES ('7', '2', 'SH', '别克英朗', '500', '/static/images/excelle.jpg', '\r\n三厢 | 1.5自动 | 乘坐5人');
        INSERT INTO `rent_car` VALUES ('8', '2', 'SH', '比亚迪秦', '200', '/static/images/byd_qin.jpg', '新能源 | 1.5T自动沪牌 | 乘坐5人 | 沪牌');
        INSERT INTO `rent_car` VALUES ('9', '2', 'SH', '雪佛兰', '235', '/static/images/klz.jpg', '三厢 | 1.6自动 | 乘坐5人');
        INSERT INTO `rent_car` VALUES ('10', '2', 'SH', '别克威朗', '200', '/static/images/2017WL.jpg', '三厢 | 1.5自动 | 乘坐5人');
        INSERT INTO `rent_car` VALUES ('11', '3', 'BJ', '大众朗逸', '100', '/static/images/ly.jpg', '三厢 | 1.6自动 | 乘坐5人');
        INSERT INTO `rent_car` VALUES ('12', '3', 'BJ', '别克英朗', '500', '/static/images/excelle.jpg', '\r\n三厢 | 1.5自动 | 乘坐5人');
        INSERT INTO `rent_car` VALUES ('13', '3', 'BJ', '比亚迪秦', '200', '/static/images/byd_qin.jpg', '新能源 | 1.5T自动沪牌 | 乘坐5人 | 沪牌');
        INSERT INTO `rent_car` VALUES ('14', '3', 'BJ', '雪佛兰', '235', '/static/images/klz.jpg', '三厢 | 1.6自动 | 乘坐5人');
        INSERT INTO `rent_car` VALUES ('15', '3', 'BJ', '别克威朗', '200', '/static/images/2017WL.jpg', '三厢 | 1.5自动 | 乘坐5人');
        INSERT INTO `rent_car` VALUES ('16', '4', 'BJ', '大众朗逸', '100', '/static/images/ly.jpg', '三厢 | 1.6自动 | 乘坐5人');
        INSERT INTO `rent_car` VALUES ('17', '4', 'BJ', '别克英朗', '500', '/static/images/excelle.jpg', '\r\n三厢 | 1.5自动 | 乘坐5人');
        INSERT INTO `rent_car` VALUES ('18', '4', 'BJ', '比亚迪秦', '200', '/static/images/byd_qin.jpg', '新能源 | 1.5T自动沪牌 | 乘坐5人 | 沪牌');
        INSERT INTO `rent_car` VALUES ('19', '4', 'BJ', '雪佛兰', '235', '/static/images/klz.jpg', '三厢 | 1.6自动 | 乘坐5人');
        INSERT INTO `rent_car` VALUES ('20', '4', 'BJ', '别克威朗', '200', '/static/images/2017WL.jpg', '三厢 | 1.5自动 | 乘坐5人');


        DROP TABLE IF EXISTS `rent_city`;
        CREATE TABLE `rent_city` (
          `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
          `name` varchar(255) DEFAULT NULL,
          `cname` varchar(255) DEFAULT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;


        INSERT INTO `rent_city` VALUES ('1', 'SH', '上海');
        INSERT INTO `rent_city` VALUES ('2', 'BJ', '北京');


        DROP TABLE IF EXISTS `rent_order`;
        CREATE TABLE `rent_order` (
          `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
          `user_id` bigint(13) DEFAULT NULL,
          `order_id` bigint(13) DEFAULT NULL,
          `create_time` int(11) DEFAULT NULL,
          `update_time` int(11) DEFAULT NULL,
          `borrow_city` text,
          `return_city` text,
          `fee` int(11) DEFAULT NULL,
          `status` int(4) DEFAULT NULL COMMENT '0:完成 1：预约 2：租车中 3：订单被取消',
          `borrow_time` int(11) DEFAULT NULL,
          `return_time` int(11) DEFAULT NULL,
          `car_id` tinyint(4) DEFAULT NULL,
          `isPayed` int(1) DEFAULT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8 COMMENT='新增数据表';

        INSERT INTO `rent_order` VALUES ('36', '6', '1708091558096', '1502265489', '1502265489', 'SH', 'SH', '160', '-1', '1502265358', '1502301358', '1', null);
        INSERT INTO `rent_order` VALUES ('37', '6', '1708091600306', '1502265630', '1502265630', 'SH', 'SH', '260', '-1', '1502265622', '1502301622', '3', null);
        INSERT INTO `rent_order` VALUES ('38', '6', '1708091607596', '1502266079', '1502266079', 'SH', 'SH', '560', '-1', '1502244000', '1502280000', '12', null);
        INSERT INTO `rent_order` VALUES ('39', '6', '1708091616026', '1502266562', '1502266562', 'SH', 'SH', '560', '0', '1502266502', '1502302502', '2', null);


        DROP TABLE IF EXISTS `rent_site`;
        CREATE TABLE `rent_site` (
          `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
          `name` varchar(255) DEFAULT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;


        INSERT INTO `rent_site` VALUES ('1', '陆家嘴');
        INSERT INTO `rent_site` VALUES ('2', '徐家汇');
        INSERT INTO `rent_site` VALUES ('3', '三里屯');
        INSERT INTO `rent_site` VALUES ('4', '长安门');

  
        DROP TABLE IF EXISTS `rent_userdata`;
        CREATE TABLE `rent_userdata` (
          `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
          `username` varchar(255) NOT NULL,
          `password` varchar(255) NOT NULL,
          `create_time` int(14) NOT NULL,
          `email` varchar(255) DEFAULT NULL,
          `update_time` int(14) NOT NULL,
          `phone` varchar(255) DEFAULT NULL,
          `price` bigint(10) DEFAULT NULL,
          `delete_time` timestamp NULL DEFAULT NULL,
          `idcard` bigint(35) DEFAULT NULL,
          `grade` int(11) DEFAULT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='新增数据表';

        INSERT INTO `rent_userdata` VALUES ('1', 'admin', 'admin', '1411111111', 'qq@qq.com', '1412354522', '13166215545', null, null, '223382195456211234', null);
        INSERT INTO `rent_userdata` VALUES ('4', 'admin', 'admin', '1501585532', null, '1501585532', 'asdasdfdsaf', null, null, null, null);
        INSERT INTO `rent_userdata` VALUES ('5', 'lhy', '1', '1501585584', null, '1501585584', '18111111111', null, null, null, null);
        INSERT INTO `rent_userdata` VALUES ('6', 'lhy', 'c4ca4238a0b923820dcc509a6f75849b', '1501585774', null, '1501585774', '18111111111', null, null, null, null);

        DROP VIEW IF EXISTS `rent_uoc`;
        CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `rent_uoc` AS select `rent_car`.`brand` AS `brand`,`rent_car`.`price` AS `price`,`rent_userdata`.`email` AS `email`,`rent_userdata`.`username` AS `username`,`rent_order`.`borrow_city` AS `borrow_city`,`rent_order`.`return_city` AS `return_city`,`rent_order`.`fee` AS `fee`,`rent_userdata`.`create_time` AS `create_time`,`rent_car`.`pic` AS `pic`,`rent_order`.`status` AS `status`,`rent_order`.`borrow_time` AS `borrow_time`,`rent_order`.`return_time` AS `return_time`,`rent_order`.`car_id` AS `car_id`,`rent_userdata`.`phone` AS `phone`,`rent_order`.`id` AS `id` from ((`rent_userdata` join `rent_order`) join `rent_car`) where ((`rent_userdata`.`id` = `rent_order`.`user_id`) and (`rent_order`.`car_id` = `rent_car`.`id`)) ;



sql;
        self::create( $sql );
    }

    /**
     * 删除数据表
     */
    public function down()
    {
        self::drop( "rent_car" );
    }
}